if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MapAnimalProtocolChangesFromRex]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[MapAnimalProtocolChangesFromRex]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



/***************************************************************************
* Name:			MapAnimalProtocolChangesFromRex
* Purpose:		Updates the Insight Agreement data with changes from InfoEd.
*
***************************************************************************/
CREATE PROCEDURE dbo.MapAnimalProtocolChangesFromRex
AS
BEGIN

	SET NOCOUNT ON

	BEGIN
		BEGIN TRY
		BEGIN TRANSACTION UpdateAnimalProtocols
		-- PIPerson
		Update Protocol Set PIPersonId = Stg.PIAdmPersonId
			From Stg_AnimalProtocol Stg
			Where Protocol.Id = Stg.ProtocolId AND
				Protocol.PIPersonId != Stg.PIAdmPersonId
				and Protocol.ModuleType = 'A'
				
		Update Protocol Set Title = Stg.sProtocolTitle
			From Stg_AnimalProtocol Stg
			Where Protocol.Id = Stg.ProtocolId AND CAST(Protocol.Title as Varchar(4000)) != CAST(Stg.sProtocolTitle as Varchar(4000))
		
		Update Protocol Set ShortTitle = Stg.sProtocolShortTitle
			From Stg_AnimalProtocol Stg
			Where Protocol.Id = Stg.ProtocolId AND Protocol.ShortTitle != Stg.sProtocolShortTitle
		
		Update Protocol Set OverallStatusId = Stg.OverallStatusId
			From Stg_AnimalProtocol Stg
			Where Protocol.Id = Stg.ProtocolId 
			AND Stg.OverallStatusID IS NOT NULL

		Update Protocol Set AdmSponsorId = Stg.AdmSponsorId
			From Stg_AnimalProtocol Stg
			Where Protocol.Id = Stg.ProtocolId 

		Update Protocol Set PIPersonId = Stg.PIAdmPersonId
			From Stg_AnimalProtocol Stg
			Where Protocol.Id = Stg.ProtocolId --AND Protocol.PIPersonId != Stg.PIPersonId
		
		UPDATE Protocol SET [DocumentNumber] = Stg.nDocumentNumber
			FROM [Stg_AnimalProtocol] Stg
			WHERE Protocol.Id = Stg.ProtocolId
		--	AND Stg.IsCUrrent = 1
			AND Protocol.IsCUrrent = 1
			AND Protocol.[DocumentNumber] != Stg.nDOcumentNumber

		UPDATE Protocol SET RexProtocolId = Stg.nAnimalProtocolId
			FROM [Stg_AnimalProtocol] Stg
			WHERE Protocol.Id = Stg.ProtocolId
		--	AND Stg.IsCUrrent = 1
			AND Protocol.IsCUrrent = 1
			AND Protocol.RexProtocolId != Stg.nAnimalProtocolId

		-- Org
		Update Protocol Set [PIFwkDomainOrganizationId] = Stg.[PIFwkDomainOrganizationId]
			From Stg_AnimalProtocol Stg
			Where Protocol.Id = Stg.ProtocolId 
			AND [Protocol].[PIFwkDomainOrganizationId] != Stg.PIFwkDOmainOrganizationId
			
		-- Ezpiration Date
		UPDATE [Protocol] SET [ExpirationDate] = Stg.[dtExpiration]
			FROM [Stg_AnimalProtocol] Stg	
			WHERE Protocol.Id = Stg.ProtocolId
			AND ISNULL(Protocol.[ExpirationDate], '') != ISNULL(Stg.dtExpiration, '')
			AND Stg.dtExpiration IS NOT NULL
			
		-- Approval Date
		UPDATE [Protocol] SET [ApprovalDate] = Stg.[dtApproval]
			FROM [Stg_AnimalProtocol] Stg	
			WHERE Protocol.Id = Stg.ProtocolId
			AND ISNULL(Protocol.[ExpirationDate], '') != ISNULL(Stg.dtApproval, '')
			AND Stg.dtApproval IS NOT NULL
		
		-- Period Start Date
		UPDATE [Protocol] SET [ProtocolPeriodStartDate] = Stg.[ProtocolPeriodStartDate]
			FROM [Stg_AnimalProtocol] Stg	
			WHERE Protocol.Id = Stg.ProtocolId
			AND ISNULL(Protocol.[ProtocolPeriodStartDate], '') != ISNULL(Stg.[ProtocolPeriodStartDate], '')
			AND Stg.[ProtocolPeriodStartDate] IS NOT NULL

		-- Type Standard
		UPDATE [Protocol] SET [ProtocolType] = 'PROTOCOLTYPE_ANIMALS'
			FROM [Stg_AnimalProtocol] Stg	
			WHERE Protocol.Id = Stg.ProtocolId
			AND Stg.sProtocolType = 'Standard'
			AND ISNULL(Protocol.[ProtocolType], '') != 'PROTOCOLTYPE_ANIMALS'

		-- Type Tissue
		UPDATE [Protocol] SET [ProtocolType] = 'PROTOCOLTYPE_ATISSUE'
			FROM [Stg_AnimalProtocol] Stg	
			WHERE Protocol.Id = Stg.ProtocolId
			AND Stg.sProtocolType = 'Tissue'
			AND ISNULL(Protocol.[ProtocolType], '') != 'PROTOCOLTYPE_ATISSUE'

			
/**		
		-- dtInserted ?
		UPDATE [Protocol] SET DateInserted = Stg.dtInserted
			FROM [Stg_AnimalProtocol] Stg	
			WHERE Protocol.Id = Stg.ProtocolId
			AND ISNULL(Protocol.DateInserted, '') != ISNULL(Stg.dtInserted, '')
			AND Stg.dtInserted IS NOT NULL
**/		
		-- Period End
		UPDATE [Protocol] SET [ProtocolPeriodEndDate] = Stg.[ProtocolPeriodEndDate]
			FROM [Stg_AnimalProtocol] Stg	
			WHERE Protocol.Id = Stg.ProtocolId
			AND ISNULL(Protocol.[ProtocolPeriodEndDate], '') != ISNULL(Stg.[ProtocolPeriodEndDate], '')
			AND Stg.[ProtocolPeriodEndDate] IS NOT NULL
		
		-- Number of Animals
		UPDATE [Protocol] SET [NumberOfAnimals] = Stg.[NumberOfAnimals]
			FROM [Stg_AnimalProtocol] Stg	
			WHERE Protocol.Id = Stg.ProtocolId
			AND ISNULL(Protocol.[NumberOfAnimals], 0) != ISNULL(Stg.[NumberOfAnimals], 0)
			AND Stg.[NumberOfAnimals] IS NOT NULL
		
		-- RadioIsotope
		UPDATE [Protocol] SET RadioIsotope = Stg.RadioIsotope
			FROM [Stg_AnimalProtocol] Stg	
			WHERE Protocol.Id = Stg.ProtocolId
			AND ISNULL(Protocol.RadioIsotope, 0) != ISNULL(Stg.RadioIsotope, 0)
			AND Stg.RadioIsotope IS NOT NULL
		
		-- Biological Agent
		UPDATE [Protocol] SET BiologicalAgent = Stg.BiologicalAgent
			FROM [Stg_AnimalProtocol] Stg	
			WHERE Protocol.Id = Stg.ProtocolId
			AND ISNULL(Protocol.BiologicalAgent, 0) != ISNULL(Stg.BiologicalAgent, 0)
			AND Stg.BiologicalAgent IS NOT NULL

		-- Toxic Chem		
		UPDATE [Protocol] SET ToxicChem = Stg.ToxicChem
			FROM [Stg_AnimalProtocol] Stg	
			WHERE Protocol.Id = Stg.ProtocolId
			AND ISNULL(Protocol.ToxicChem, 0) != ISNULL(Stg.ToxicChem, 0)
			AND Stg.ToxicChem IS NOT NULL
	
		-- Tissue Test
		UPDATE [Protocol] SET TissueTest = Stg.TissueTest
			FROM [Stg_AnimalProtocol] Stg	
			WHERE Protocol.Id = Stg.ProtocolId
			AND ISNULL(Protocol.TissueTest, 0) != ISNULL(Stg.TissueTest, 0)
			AND Stg.TissueTest IS NOT NULL
				
		-- Species
		UPDATE [Protocol] SET [SpeciesCode] = Stg.[SpeciesCode]
			FROM [Stg_AnimalProtocol] Stg	
			WHERE Protocol.Id = Stg.ProtocolId
			AND ISNULL(Protocol.[SpeciesCode], '') != ISNULL(Stg.[SpeciesCode], '')
			AND Stg.[SpeciesCode] IS NOT NULL
			
		-- Rex Protocol ID
		UPDATE [protocol] SET RexProtocoLId = Stg.nAnimalProtocolId
			FROM [Stg_AnimalProtocol] Stg
			WHERE [Protocol].Id = Stg.[ProtocolId]
			AND [protocol].RexProtocolId != Stg.[nAnimalProtocolID]

		-- Pending IACUC
		UPDATE Protocol SET PendingIRB = STg.PendingIACUC
			FROM Stg_AnimalProtocol Stg
			WHERE Protocol.Id = Stg.ProtocolId

		-- Protocol abstract
		-->Since protocolAbstract is a new column added to the protocol table,check if this column exists before updating the column.
		IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS 
			WHERE TABLE_NAME = 'Protocol' AND COLUMN_NAME = 'ProtocolAbstract') 
			BEGIN
				   UPDATE Protocol SET ProtocolAbstract = Stg.RTFProtocolAbstract  
				   FROM Stg_AnimalProtocol Stg  
				   WHERE Protocol.Id = Stg.ProtocolId 
				   and stg.HasRTFAbstract = 1 
			END
		
	----------------------------------------------------------------------
	-- COMMIT THE TRANSACTION!!!
	-- And end the try block, then handle any errors.
	----------------------------------------------------------------------
	COMMIT TRANSACTION UpdateAnimalProtocols
	END TRY

	BEGIN CATCH
		DECLARE 
			@ErrorMessage    NVARCHAR(4000),
			@ErrorNumber     INT,
			@ErrorSeverity   INT,
			@ErrorState      INT,
			@ErrorLine       INT,
			@ErrorProcedure  NVARCHAR(200);

		-- Assign variables to error-handling functions that 
		-- capture information for RAISERROR.
		SELECT 
			@ErrorNumber = ERROR_NUMBER(),
			@ErrorSeverity = ERROR_SEVERITY(),
			@ErrorState = ERROR_STATE(),
			@ErrorLine = ERROR_LINE(),
			@ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');

		-- Building the message string that will contain original
		-- error information.
		SELECT @ErrorMessage = 
			N'Error %d, Level %d, State %d, Procedure %s, Line %d, ' + 
				'Message: '+ ERROR_MESSAGE();
	
		-----------------------------------------------------
		-- ROLLBACK the transaction if we got here....
		-----------------------------------------------------
		IF XACT_STATE() <> 0 Rollback transaction UpdateAnimalProtocols

		-- Raise an error: msg_str parameter of RAISERROR will contain
		-- the original error information.
		RAISERROR 
			(
			@ErrorMessage, 
			@ErrorSeverity, 
			1,               
			@ErrorNumber,    -- parameter: original error number.
			@ErrorSeverity,  -- parameter: original error severity.
			@ErrorState,     -- parameter: original error state.
			@ErrorProcedure, -- parameter: original error procedure name.
			@ErrorLine       -- parameter: original error line number.
			);
			return 99
		END CATCH;

	END
		
	SET NOCOUNT OFF

END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO